package com.example.spsaveqqty.attraction;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.example.spsaveqqty.travelguidesdatabase.DatabaseHelper;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;

public class AttractionDao {
    private DatabaseHelper mHelper;
    private SQLiteDatabase db = null;


    public AttractionDao(Context context) {
        mHelper = new DatabaseHelper(context);
    }

    public void insertComment(Comment comment){
         db = mHelper.getWritableDatabase();
         String sql = "insert into comment(star,content,attrac_name,username) values(?,?,?,?)";
//        Log.i("--------befor---------", comment.toString());

        db.execSQL(sql, new Object[]{comment.getStar(), comment.getContent(), comment.getAttrac_name(), comment.getUsername()});
//        Log.i("------after-----------", "??????????????");

        db.close();
    }

    public void uodateScore(String attracName){
        double score=0;
        db = mHelper.getWritableDatabase();
        String sql = "select sum(star)/count(attrac_name) score from comment where attrac_name=?";
        Cursor cursor = db.rawQuery(sql, new String[]{attracName});

        if (null != cursor) {
            while (cursor.moveToNext()) {
                // double result = new BigDecimal(data).setScale(1, BigDecimal.ROUND_HALF_UP).doubleValue();
                Double score0 = (cursor.getDouble(cursor.getColumnIndex("score")));
                score = new BigDecimal(score0).setScale(1, BigDecimal.ROUND_HALF_UP).doubleValue();
            }
        }
        String sql2 = "update attraction set score=? where attrac_name=?";
        db.execSQL(sql2, new Object[]{score, attracName});
        db.close();

    }

    //查看评论
    public List<Comment> queryComment(String attrac_name){
        List<Comment> commentList = new ArrayList<Comment>();
        try {
            db = mHelper.getReadableDatabase();

            Cursor cursor = db.rawQuery("select * from comment where attrac_name=?", new String[]{attrac_name});
            if (null != cursor) {
                while (cursor.moveToNext()) {

                    int comment_id = cursor.getInt(cursor.getColumnIndex("comment_id"));
                    String content = cursor.getString(cursor.getColumnIndex("content"));
                    String username = cursor.getString(cursor.getColumnIndex("username"));
                    String attracname = cursor.getString(cursor.getColumnIndex("attrac_name"));
                    Double star = cursor.getDouble(cursor.getColumnIndex("star"));


                    Comment bean = new Comment();
                    bean.setComment_id(comment_id);
                    bean.setAttrac_name(attracname);
                    bean.setContent(content);
                    bean.setUsername(username);
                    bean.setStar(star);

                    commentList.add(bean);
                }
            }else{
                commentList = null;
            }
            cursor.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (null != db) {
                db.close();
            }
        }
        return commentList;

    }


    public List<Attraction> select(String name) {
        List<Attraction> attracList = new ArrayList<Attraction>();
        try {
            db = mHelper.getReadableDatabase();

            Cursor cursor = db.rawQuery("select * from attraction where attrac_name=?", new String[]{name});
            if (null != cursor) {
                while (cursor.moveToNext()) {
                    int id = cursor.getInt(cursor.getColumnIndex("attrac_id"));
                    String aname = cursor.getString(cursor.getColumnIndex("attrac_name"));
                    String tip = cursor.getString(cursor.getColumnIndex("tip"));
                    String address = cursor.getString(cursor.getColumnIndex("address"));
                    String pic = cursor.getString(cursor.getColumnIndex("pic"));
                    Double score = cursor.getDouble(cursor.getColumnIndex("score"));
                    String notice = cursor.getString(cursor.getColumnIndex("notice"));
                    String opentime = cursor.getString(cursor.getColumnIndex("opentime"));
                    String policy = cursor.getString(cursor.getColumnIndex("policy"));
                    String price = cursor.getString(cursor.getColumnIndex("price"));
                    String notes = cursor.getString(cursor.getColumnIndex("notes"));
                    String introduction = cursor.getString(cursor.getColumnIndex("introduction"));


                    Attraction bean = new Attraction();
                    bean.setAttrac_id(id);
                    bean.setAttract_name(aname);
                    bean.setIntroduction(introduction);
                    bean.setNotes(notes);
                    bean.setPrice(price);
                    bean.setPolicy(policy);
                    bean.setOpentime(opentime);
                    bean.setNotice(notice);
                    bean.setTip(tip);
                    bean.setAddress(address);
                    bean.setPic(pic);
                    bean.setScore(score);

                    attracList.add(bean);
                }
            }else{
              //  attracList = null;
            }
            cursor.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (null != db) {
                db.close();
            }
        }
        return attracList;

    }

    public List<Attraction> selectByOrder(){
        List<Attraction> rankList = new ArrayList<Attraction>();
        try {
            db = mHelper.getReadableDatabase();

            Cursor cursor = db.rawQuery("select * from attraction order by score desc, attrac_id asc", null);
            if (null != cursor) {
                while (cursor.moveToNext()) {

                    String aname = cursor.getString(cursor.getColumnIndex("attrac_name"));
                    String tip = cursor.getString(cursor.getColumnIndex("tip"));
                    String address = cursor.getString(cursor.getColumnIndex("address"));
                    String pic = cursor.getString(cursor.getColumnIndex("pic"));
                    Double score = cursor.getDouble(cursor.getColumnIndex("score"));


                    Attraction bean = new Attraction();
                    bean.setAttract_name(aname);
                    bean.setTip(tip);
                    bean.setAddress(address);
                    bean.setPic(pic);
                    bean.setScore(score);

                    rankList.add(bean);
                }
            }else{
                rankList = null;
            }
            cursor.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (null != db) {
                db.close();
            }
        }
        return rankList;

    }

}
